/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * ReportGui.java
 *
 * Created on Apr 18, 2011, 6:15:32 AM
 */

package Reporting;

import java.sql.*;
import Database.*;
import java.util.Date;
import javax.xml.*;
import java.io.*;
import java.io.File;
import org.w3c.dom.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.swing.JOptionPane;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
/**
 *
 * @author Matt
 */
public class ReportGui extends javax.swing.JFrame {

    /** Creates new form ReportGui */
    public ReportGui() {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jSpinner1 = new javax.swing.JSpinner();
        dateFromTextField = new javax.swing.JTextField();
        dateToTextField = new javax.swing.JTextField();
        dateFromLabel = new javax.swing.JLabel();
        dateToLabel = new javax.swing.JLabel();
        generateReportButton = new javax.swing.JButton();
        cancelButton = new javax.swing.JButton();
        jButton1 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        dateFromLabel.setText("Date from: (YYYY-MM-DD HH:MM:SS)");

        dateToLabel.setText("Date to: (YYYY-MM-DD HH:MM:SS)");

        generateReportButton.setText("Generate Report");
        generateReportButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                generateReportButtonActionPerformed(evt);
            }
        });

        cancelButton.setText("Cancel");
        cancelButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                cancelButtonActionPerformed(evt);
            }
        });

        jButton1.setText("DELETE records before this date");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(108, 108, 108)
                        .addComponent(generateReportButton)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(cancelButton))
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(dateToLabel, javax.swing.GroupLayout.Alignment.TRAILING)
                            .addComponent(dateFromLabel))
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(dateToTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 255, Short.MAX_VALUE)
                            .addComponent(dateFromTextField, javax.swing.GroupLayout.DEFAULT_SIZE, 255, Short.MAX_VALUE))))
                .addGap(18, 18, 18)
                .addComponent(jButton1)
                .addGap(55, 55, 55))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(dateFromLabel)
                    .addComponent(dateFromTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton1))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(dateToLabel)
                    .addComponent(dateToTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(generateReportButton)
                    .addComponent(cancelButton))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void generateReportButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_generateReportButtonActionPerformed
        // TODO add your handling code here:

        DBConnection dbConnector = new DBConnection();
        Connection conn = dbConnector.getConn();
        StringBuilder htmlOutput = new StringBuilder();

        try{
        String timeStampFrom = dateFromTextField.getText();
        String timeStampTo = dateToTextField.getText();
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder parser = factory.newDocumentBuilder();
            Document doc = parser.newDocument();
            Element root = doc.createElement("Overall_Report");
            htmlOutput.append("<html><head><title>Visualization Report</title></head><body>");
            htmlOutput.append("<h1>Visualization Report</h1><br/><h2>From " + timeStampFrom + " to " + timeStampTo + "</h2><br/>");
            doc.appendChild(root);
            Element report1Element = doc.createElement("Report_1");
            Element report2Element = doc.createElement("Report_2");
            Element report3Element = doc.createElement("Report_3");
            root.appendChild(report1Element);
            root.appendChild(report2Element);
            root.appendChild(report3Element);

            htmlOutput.append("<b>Report 1: country, city, visualization</b>");
            htmlOutput.append("<table><tr bgcolor=\"#999999\"><th>Country</th><th>City</th><th>Visualization</th><th>Count</th></tr>");

            String selectTest = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                    + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' group by v.country";
                PreparedStatement ps = null;
                ps = conn.prepareStatement(selectTest);
                ResultSet rs = ps.executeQuery();
                while(rs.next()){

                    String nameCountry = rs.getString("country");
                    String countCountry = rs.getString("timesUsed");
                    htmlOutput.append("<tr bgcolor=\"#bbbbbb\"><td>" + nameCountry + "</td><td>&nbsp;</td><td>&nbsp;</td><td>" + countCountry + "</td></tr>");
                    Element countryElement = doc.createElement("Country");
                    countryElement.setAttribute("name", nameCountry);
                    countryElement.setAttribute("count", countCountry);
                    report1Element.appendChild(countryElement);
                    String selectTest2 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                    + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' and v.country='"+nameCountry+"' group by v.city";
                    PreparedStatement ps2 = null;
                    ps2 = conn.prepareStatement(selectTest2);
                    ResultSet rs2 = ps2.executeQuery();
                    while(rs2.next()){

                        String nameCity = rs2.getString("city");
                        String countCity = rs2.getString("timesUsed");
                        htmlOutput.append("<tr bgcolor=\"#dddddd\"><td>&nbsp;</td><td>" + nameCity + "</td><td>&nbsp;</td><td>" + countCity + "</td></tr>");
                        Element cityElement = doc.createElement("City");
                        cityElement.setAttribute("name", nameCity);
                        cityElement.setAttribute("count", countCity);
                        countryElement.appendChild(cityElement);

                        String selectTest3 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                        + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' and v.country='"+nameCountry+"' and v.city='"+nameCity+"' group by v.visualizationName";
                        //System.out.println(selectTest3);
                        PreparedStatement ps3 = null;
                        ps3 = conn.prepareStatement(selectTest3);
                        ResultSet rs3 = ps3.executeQuery();
                        while(rs3.next()){
                            String nameVis = rs3.getString("visualizationName");
                            String countVis = rs3.getString("timesUsed");
                            htmlOutput.append("<tr><td>&nbsp;</td><td>&nbsp;</td><td>" + nameVis + "</td><td>" + countVis + "</td></tr>");
                            Element visElement = doc.createElement("Visualization");
                            visElement.setAttribute("name", nameVis);
                            visElement.setAttribute("count", countVis);
                            cityElement.appendChild(visElement);

                        }
                    }
                    
            }

            htmlOutput.append("</table><br/>");
            htmlOutput.append("<b>Report 2: visualization, country, city</b>");
            htmlOutput.append("<table><tr bgcolor=\"#999999\"><th>Visualization</th><th>Country</th><th>City</th><th>Count</th></tr>");

                
                String selectTest4 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                    + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' group by v.visualizationName";
                PreparedStatement ps4 = null;
                ps4 = conn.prepareStatement(selectTest4);
                ResultSet rs4 = ps4.executeQuery();
                while(rs4.next()){

                    String nameVis = rs4.getString("visualizationName");
                    String countVis = rs4.getString("timesUsed");
                    htmlOutput.append("<tr bgcolor=\"#bbbbbb\"><td>" + nameVis + "</td><td>&nbsp;</td><td>&nbsp;</td><td>" + countVis + "</td></tr>");
                    Element visElement = doc.createElement("Visualization");
                    visElement.setAttribute("name", nameVis);
                    visElement.setAttribute("count", countVis);
                    report2Element.appendChild(visElement);
                    String selectTest5 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                    + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' and v.visualizationName='"+nameVis+"' group by v.country";
                    PreparedStatement ps5 = null;
                    ps5 = conn.prepareStatement(selectTest5);
                    ResultSet rs5 = ps5.executeQuery();
                    while(rs5.next()){

                        String nameCountry = rs5.getString("country");
                        String countCountry = rs5.getString("timesUsed");
                        htmlOutput.append("<tr bgcolor=\"#dddddd\"><td>&nbsp;</td><td>" + nameCountry + "</td><td>&nbsp;</td><td>" + countCountry + "</td></tr>");
                        Element countryElement = doc.createElement("Country");
                        countryElement.setAttribute("name", nameCountry);
                        countryElement.setAttribute("count", countCountry);
                        visElement.appendChild(countryElement);

                        String selectTest6 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                        + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' and v.country='"+nameCountry+"' and v.visualizationName='"+nameVis+"' group by v.city";
                        //System.out.println(selectTest6 + "SECOND");
                        PreparedStatement ps6 = null;
                        ps6 = conn.prepareStatement(selectTest6);
                        ResultSet rs6 = ps6.executeQuery();
                        while(rs6.next()){
                            String nameCity = rs6.getString("city");
                            String countCity = rs6.getString("timesUsed");
                            htmlOutput.append("<tr><td>&nbsp;</td><td>&nbsp;</td><td>" + nameCity + "</td><td>" + countCity + "</td></tr>");
                            Element cityElement = doc.createElement("City");
                            cityElement.setAttribute("name", nameCity);
                            cityElement.setAttribute("count", countCity);
                            countryElement.appendChild(cityElement);

                        }
                    }

            }
                htmlOutput.append("</table><br/>");
            htmlOutput.append("<b>Report 3: country, city, username</b>");
            htmlOutput.append("<table><tr bgcolor=\"#999999\"><th>Country</th><th>City</th><th>Username</th><th>Count</th></tr>");

                
                String selectTest7 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                    + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' group by v.country";
                PreparedStatement ps7 = null;
                ps7 = conn.prepareStatement(selectTest7);
                ResultSet rs7 = ps7.executeQuery();
                while(rs7.next()){

                    String nameCountry = rs7.getString("country");
                    String countCountry = rs7.getString("timesUsed");
                    htmlOutput.append("<tr bgcolor=\"#bbbbbb\"><td>" + nameCountry + "</td><td>&nbsp;</td><td>&nbsp;</td><td>" + countCountry + "</td></tr>");
                    Element countryElement = doc.createElement("Country");
                    countryElement.setAttribute("name", nameCountry);
                    countryElement.setAttribute("count", countCountry);
                    report3Element.appendChild(countryElement);
                    String selectTest8 = "select count(v.visualizationName) timesUsed, v.visualizationName, v.country, v.city "
                    + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' and v.country='"+nameCountry+"' group by v.city";
                    PreparedStatement ps8 = null;
                    ps8 = conn.prepareStatement(selectTest8);
                    ResultSet rs8 = ps8.executeQuery();
                    while(rs8.next()){

                        String nameCity = rs8.getString("city");
                        String countCity = rs8.getString("timesUsed");
                        htmlOutput.append("<tr bgcolor=\"#dddddd\"><td>&nbsp;</td><td>" + nameCity + "</td><td>&nbsp;</td><td>" + countCity + "</td></tr>");
                        Element cityElement = doc.createElement("City");
                        cityElement.setAttribute("name", nameCity);
                        cityElement.setAttribute("count", countCity);
                        countryElement.appendChild(cityElement);

                        String selectTest9 = "select count(v.visualizationName) timesUsed, v.username, v.country, v.city "
                        + "from Visualization v where v.timeStamp between '"+timeStampFrom+"' and '"+timeStampTo+"' and v.country='"+nameCountry+"' and v.city='"+nameCity+"' group by v.username";
                        //System.out.println(selectTest9);
                        PreparedStatement ps9 = null;
                        ps9 = conn.prepareStatement(selectTest9);
                        ResultSet rs9 = ps9.executeQuery();
                        while(rs9.next()){
                            String nameUser = rs9.getString("username");
                            if (nameUser == null){
                                    nameUser = "Not Logged In";
                                    //System.out.println("NOT LOGGED IN");
                            }
                            String countUser = rs9.getString("timesUsed");
                            htmlOutput.append("<tr><td>&nbsp;</td><td>&nbsp;</td><td>" + nameUser + "</td><td>" + countUser + "</td></tr>");
                            Element userElement = doc.createElement("User");
                            userElement.setAttribute("name", nameUser);
                            userElement.setAttribute("count", countUser);
                            cityElement.appendChild(userElement);

                        }
                    }
                    
            }

            htmlOutput.append("</table>");
            htmlOutput.append("</body></html>");

            Source src = new DOMSource(doc);
            TransformerFactory tranFactory = TransformerFactory.newInstance();
            Transformer aTransformer = tranFactory.newTransformer();
            String filename = File.separator + "tmp";
            JFileChooser fc = new JFileChooser(new File(filename));
            JFrame frame = new JFrame();
            fc.setAcceptAllFileFilterUsed(false);
            MyXMLFilter xmlf = new MyXMLFilter();
            MyHTMLFilter htmlf = new MyHTMLFilter();
            fc.addChoosableFileFilter(htmlf);
            fc.addChoosableFileFilter(xmlf);
            fc.showSaveDialog(frame);
            File selFile = fc.getSelectedFile();
            if(fc.getFileFilter() == xmlf && !selFile.getPath().endsWith(".xml"))
                selFile = new File(selFile.getPath() + ".xml");
            if(fc.getFileFilter() == htmlf && !selFile.getPath().endsWith(".html"))
                selFile = new File(selFile.getPath() + ".html");

            if (fc.getFileFilter() == xmlf) {
                //System.out.println(selFile.toString());
                Result dest = new StreamResult(selFile);
                aTransformer.transform(src, dest);
            } else {
                FileWriter writer = new FileWriter(selFile);
                writer.write(htmlOutput.toString() + "\n");
                writer.close();
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }



    }//GEN-LAST:event_generateReportButtonActionPerformed

    private void cancelButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cancelButtonActionPerformed
        // TODO add your handling code here:
        System.exit(0);
    }//GEN-LAST:event_cancelButtonActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        String sql1 = "DELETE FROM Visualization WHERE timeStamp < ?";
        String sql2 = "DELETE FROM Connections WHERE timeStamp < ?";
        try {
            PreparedStatement ps1 = DBConnection.getConn().prepareStatement(sql1);
            PreparedStatement ps2 = DBConnection.getConn().prepareStatement(sql2);
            ps1.setString(1, this.dateFromLabel.getText());
            ps2.setString(1, this.dateFromLabel.getText());
            ps1.executeUpdate();
            ps2.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }//GEN-LAST:event_jButton1ActionPerformed

    /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new ReportGui().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton cancelButton;
    private javax.swing.JLabel dateFromLabel;
    private javax.swing.JTextField dateFromTextField;
    private javax.swing.JLabel dateToLabel;
    private javax.swing.JTextField dateToTextField;
    private javax.swing.JButton generateReportButton;
    private javax.swing.JButton jButton1;
    private javax.swing.JSpinner jSpinner1;
    // End of variables declaration//GEN-END:variables

}
